<?php

ob_start();
session_start();
include_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/configs/database.php');
/*

  /* Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

if (PHP_SAPI == 'cli')
    die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/libraries/PHPExcel_1.7.9_doc/Classes/PHPExcel.php');

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
        ->setLastModifiedBy("Maarten Balliauw")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
// Lay du lieu thong ke


$madv = $_SESSION['madv'];
$tinhtrang = $_SESSION['tinhtrang'];
	$madv = $_SESSION['madv'];
	$congsuat = $_SESSION['congsuat'];
	
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1', 'Công Ty Điện Lực Cà Mau');
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A2', 'Địa chỉ: 22 Ngô Quyền P2 TP Cà Mau');
	$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('B1', 'CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM');
	$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('B2', 'Độc Lập - Tự Do - Hạnh Phúc');
	$objPHPExcel->getActiveSheet()->mergeCells('B1:C1');
	$objPHPExcel->getActiveSheet()->mergeCells('B2:C2');
				$objPHPExcel->getActiveSheet()->getStyle('A1:C2')->applyFromArray(
						array(
							'font' => array(
								'size' => 13,
								'bold' => true
										)
							)
				);
				$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(
						array(
							'font' => array(
								'size' => 12,
								'italic' => true
	
							),
						)
				);
							$objPHPExcel->getActiveSheet()->getStyle('A1:C2')->applyFromArray(
						array(
							   'alignment' => array(
								'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
								'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
							)
						)
				);
	
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('C4', 'Ngày Tạo: ' . date('d/m/Y', time()));
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A4', 'Liên hệ: Nguyễn Phạm Thanh Tú/CBKT/Phòng KH-KT');
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A5', 'Điện thoại/Fax/Email: ');
	$objPHPExcel->getActiveSheet()->getStyle('A4:C5')->applyFromArray(
			array(
				'font' => array(
					'size' => 12
				//'bold' => true
				),
				'alignment' => array(
					'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
				)
			)
	);

if ($madv != 'tatca') {
    $db = new database();
    $db->setQuery("select * from don_vi where MA_DON_VI = '" . $madv . "'");
    $re = $db->fetchAll();
    $row = mysql_fetch_array($re);
    $tendv = $row['TEN_DON_VI'];
    //Danh sach may tinh so huu
    $db->setQuery("select * from chi_tiet_so_huu where MA_DON_VI = '" . $madv . "' group by SO_N0");
    $dong = $db->fetchAll();

    $mang = array();
    $i = 0;
    while ($in = mysql_fetch_array($dong)) {
        $mang[$i] = $in['SO_N0'];
        $i++;
    }

    $mang2 = array();
    //Lay danh sach may don vi dang so huu	
    $k = 0;
    for ($j = 0; $j < $i; $j++) {//vong for 1
        $db->setQuery("select * from chi_tiet_so_huu where SO_N0 = '" . $mang[$j] . "' order by NGAY_CHUYEN_DEN desc");
        $truyvan = $db->fetchAll();
        $madvtimdc = mysql_fetch_array($truyvan);

        if ($madvtimdc['MA_DON_VI'] == $madv) {

            $mang2[$k] = $madvtimdc['SO_N0'];

            $k++;
        }
    }//ket thuc for 1
    //Kiểm tra đơn vị có sở hữu máy biến áp ko?
    if (count($mang2) == 0) {
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A6', 'Hiện tại đơn vị ' . $tendv . ' không sở hữu máy biến áp nào để thống kê');
    } else {//Kiểm tra đơn vị có sở hữu máy biến áp ko?
        // xu ly tuy theo tinh trang
        if ($tinhtrang != 'tatca' && $tinhtrang != 'khongco') {// vong neu tinh trang la tat ca
            $dem = 0;
            for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                $db->setQuery("select * from chi_tiet_tt_may where SO_N0 = '" . $mang2[$i] . "' order by TINH_TRANG_NGAY desc");
                $chitiettt = $db->fetchAll();
                $ketqua = mysql_fetch_array($chitiettt);
                if (strtolower($ketqua['TEN_TINH_TRANG_MAY']) == strtolower($tinhtrang))
                    $dem++;
            }//ket thuc for 2
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A6', 'Bảng Thống Kê Theo Tình Trạng')
                    ->setCellValue('A7', 'Tên Đơn Vị')
                    ->setCellValue('B7', 'Tên Tình Trạng')
                    ->setCellValue('C7', 'Số Lượng')
                    ->setCellValue('A8', $tendv)
                    ->setCellValue('B8', $tinhtrang)
                    ->setCellValue('C8', $dem)
                    ->setCellValue('A9', 'Tổng số: ' . $dem . ' máy');
            // merge cho tieu de bang
            $objPHPExcel->getActiveSheet()->mergeCells('A6:C6');
            // mergecell cho dong tong so
            $objPHPExcel->getActiveSheet()->mergeCells('A9:C9');
            // set autoresize cho dong tieu de bang
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);

            // mau` bang
            $objPHPExcel->getActiveSheet()->getStyle('A6:' .
                    $objPHPExcel->getActiveSheet()->getHighestColumn() .
                    $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
                    array(
                        'font' => array(
                            'size' => 12
                        ),
                        'borders' => array(
                            'allborders' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN,
                                'color' => array('rgb' => '#000000'),
                            )
                        ),
                        'alignment' => array(
                            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                        )
                    )
            );
// Set format for tableheader
            $objPHPExcel->getActiveSheet()->getStyle('A6:C7')->applyFromArray(
                    array(
                        'font' => array(
                            'bold' => true,
                            'size' => 13,
                        ),
                        'borders' => array(
                            'top' => array(
                                'style' => PHPExcel_Style_Border::BORDER_THIN
                            )
                        )
                    )
            );

            // set alignment cho dong tong so sau khi mergecell
            $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        } // ket thuc vong neu co ten tinh trang 
        else {
            if ($tinhtrang == 'khongco') { //vong if cua tinh trang ko co
                $dem = 0;
                for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                    $db->setQuery("select * from chi_tiet_tt_may where SO_N0 = '" . $mang2[$i] . "'");
                    $chitiettt = $db->fetchAll();
                    $ketqua = mysql_fetch_array($chitiettt);
                    if ($ketqua['SO_N0'] == NULL)
                        $dem++;
                }//ket thuc for 2
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A6', 'Bảng Thống Kê Theo Tình Trạng')
                        ->setCellValue('A7', 'Tên Đơn Vị')
                        ->setCellValue('B7', 'Tên Tình Trạng')
                        ->setCellValue('C7', 'Số Lượng')
                        ->setCellValue('A8', $tendv)
                        ->setCellValue('B8', 'Không có tình trạng')
                        ->setCellValue('C8', $dem)
                        ->setCellValue('A9', 'Tổng số: ' . $dem . ' máy');
                // merge cho tieu de bang
                $objPHPExcel->getActiveSheet()->mergeCells('A6:C6');
                // mergecell cho dong tong so
                $objPHPExcel->getActiveSheet()->mergeCells('A9:C9');
                // set autoresize cho dong tieu de bang
                $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
                $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
                $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);

                // mau` bang
                $objPHPExcel->getActiveSheet()->getStyle('A6:' .
                        $objPHPExcel->getActiveSheet()->getHighestColumn() .
                        $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
                        array(
                            'font' => array(
                                'size' => 12  
                            ),
                            'borders' => array(
                                'allborders' => array(
                                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                                    'color' => array('rgb' => '#000000'),
                                )
                            ),
                             'alignment' => array(
                                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            )
                        )
                );
// Set format for tableheader
                $objPHPExcel->getActiveSheet()->getStyle('A6:C7')->applyFromArray(
                        array(
                            'font' => array(
                                'bold' => true,
                                'size' => 13,
                            ),
                            'borders' => array(
                                'top' => array(
                                    'style' => PHPExcel_Style_Border::BORDER_THIN
                                )
                            )
                        )
                );

                // set alignment cho dong tong so sau khi mergecell
                $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            }// ket thuc vong if cua tinh trang ko co
            else if ($tinhtrang == 'tatca') {//neu tinh trang la tat ca
                // Truong hop tat ca cac tinh trang
                $tongso = 0;
                $cellcount = 8;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A6', 'Bảng Thống Kê Theo Tình Trạng')
                        ->setCellValue('A7', 'Tên Đơn Vị')
                        ->setCellValue('B7', 'Tên Tình Trạng')
                        ->setCellValue('C7', 'Số Lượng')
                        ->setCellValue('A8', $tendv);
                $db->setQuery("select * from tinh_trang_may");
                $re = $db->fetchAll();

                while ($row = mysql_fetch_array($re)) {

                    $tt = $row['TEN_TINH_TRANG_MAY'];
                    $dem = 0;
                    for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                        $db->setQuery("select * from chi_tiet_tt_may where SO_N0 = '" . $mang2[$i] . "' order by TINH_TRANG_NGAY desc");
                        $chitiettt = $db->fetchAll();
                        $ketqua = mysql_fetch_array($chitiettt);
                        if (strtolower($ketqua['TEN_TINH_TRANG_MAY']) == strtolower($tt))
                            $dem++;
                    }//ket thuc for 2

                    if ($dem != 0) {

                        $tongso = $tongso + $dem;
                        $objPHPExcel->setActiveSheetIndex(0)
                                ->setCellValue('B' . $cellcount, $tt)
                                ->setCellValue('C' . $cellcount, $dem);
                        $cellcount++;
                    }
                }//het vong while
                //xu ly voi cac may ko co tinh trang
                $dem = 0;
                for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                    $db->setQuery("select * from chi_tiet_tt_may where SO_N0 = '" . $mang2[$i] . "'");
                    $chitiettt = $db->fetchAll();
                    $ketqua = mysql_fetch_array($chitiettt);

                    if ($ketqua['SO_N0'] == NULL)
                        $dem++;
                }//ket thuc for 2
                if ($dem != 0) {

                    $tongso = $tongso + $dem;
                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('B' . $cellcount, 'Không có tình trạng')
                            ->setCellValue('C' . $cellcount, $dem);
                    $cellcount++;
                }
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, 'Tổng số: ' . $tongso . ' máy');
                $count_merge = $cellcount - 1;
                // merge cho tieu de bang
                $objPHPExcel->getActiveSheet()->mergeCells('A6:C6');
                // mergecell cho don vi
                $objPHPExcel->getActiveSheet()->mergeCells('A8:' . 'A' . $count_merge);
                // mergecell cho dong tong so
                $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
                // set autoresize cho dong tieu de bang
                $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
                $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
                $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);

                // set alignment 
                $objPHPExcel->getActiveSheet()->getStyle('A6:' .
                        $objPHPExcel->getActiveSheet()->getHighestColumn() .
                        $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
                        array(
                            'font' => array(
                                'size' => 12,
                              ),
                            'borders' => array(
                                'allborders' => array(
                                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                                    'color' => array('rgb' => '#000000'),
                                )
                            ),
                                'alignment' => array(
                                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            )
                        )
                );

// Set format for tableheader
                $objPHPExcel->getActiveSheet()->getStyle('A6:C7')->applyFromArray(
                        array(
                            'font' => array(
                                'bold' => true,
                                'size' => 13,

                            )
                        )
                );

                // set alignment cho dong tong so sau khi mergecell
                $objPHPExcel->getActiveSheet()->getStyle('A' . ($cellcount))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle('A' . ($cellcount))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            }//ket thuc neu tinh trang la tat ca
        }
    }//Kiểm tra đơn vị có sở hữu máy biến áp ko?
} else {//vong else cua truong hop dv la tat ca
    $db1 = new database();
    $cellcount = 8;
    $count4merge = 8;
    $tongmaycacdv = 0;
    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A6', 'Bảng Thống Kê Theo Tình Trạng')
            ->setCellValue('A7', 'Tên Đơn Vị')
            ->setCellValue('B7', 'Tên Tình Trạng')
            ->setCellValue('C7', 'Số Lượng');

    $dsdv = "";
    $db1->setQuery("select * from don_vi as dv, tai_khoan as tk where dv.MA_DON_VI = tk.MA_DON_VI and  tk.QUYEN = 3");
    $re = $db1->fetchAll();
    while ($row = mysql_fetch_array($re)) {
        $tendv = $row['TEN_DON_VI'];
        $madv = $row['MA_DON_VI'];
        //Danh sach may tinh so huu
        $db1->setQuery("select * from chi_tiet_so_huu where MA_DON_VI = '" . $madv . "' group by SO_N0");
        $dong = $db1->fetchAll();
        $mang = array();
        $i = 0;
        while ($in = mysql_fetch_array($dong)) {
            $mang[$i] = $in['SO_N0'];
            $i++;
        }

        $mang2 = array();
        //Lay danh sach may don vi dang so huu	
        $k = 0;
        for ($j = 0; $j < $i; $j++) {//vong for 1
            $db1->setQuery("select * from chi_tiet_so_huu where SO_N0 = '" . $mang[$j] . "' order by NGAY_CHUYEN_DEN desc");
            $truyvan = $db1->fetchAll();
            $madvtimdc = mysql_fetch_array($truyvan);
            if ($madvtimdc['MA_DON_VI'] == $madv) {
                $mang2[$k] = $madvtimdc['SO_N0'];
                $k++;
            }
        }//ket thuc for 1
        //Kiểm tra đơn vị có sở hữu máy biến áp ko?
        if (count($mang2) == 0) {
            $dsdv = $dsdv . ", " . $tendv;
        } else {//Kiểm tra đơn vị có sở hữu máy biến áp ko?
            // xu ly tuy theo tinh trang
            if ($tinhtrang != 'tatca' && $tinhtrang != 'khongco') {// vong neu tinh trang la tat ca
                $dem = 0;
                for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                    $db1->setQuery("select * from chi_tiet_tt_may where SO_N0 = '" . $mang2[$i] . "' order by TINH_TRANG_NGAY desc");
                    $chitiettt = $db1->fetchAll();
                    $ketqua = mysql_fetch_array($chitiettt);
                    if (strtolower($ketqua['TEN_TINH_TRANG_MAY']) == strtolower($tinhtrang))
                        $dem++;
                }//ket thuc for 2

                $tongmaycacdv = $tongmaycacdv + $dem;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, $tendv)
                        ->setCellValue('B' . $cellcount, $tinhtrang)
                        ->setCellValue('C' . $cellcount, $dem);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount . ':C' . $cellcount)->applyFromArray(
                        array(
                            'alignment' => array(
                                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            ),
                        )
                );
                $cellcount++;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, 'Tổng số: ' . $dem . ' máy');
                $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $cellcount++;
            } // ket thuc vong neu co ten tinh trang 
            else {
                if ($tinhtrang == 'khongco') { //vong if cua tinh trang ko co		
                    $dem = 0;
                    for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                        $db1->setQuery("select * from chi_tiet_tt_may where SO_N0 = '" . $mang2[$i] . "'");
                        $chitiettt = $db1->fetchAll();
                        $ketqua = mysql_fetch_array($chitiettt);
                        if ($ketqua['SO_N0'] == NULL)
                            $dem++;
                    }//ket thuc for 2
                    $tongmaycacdv = $tongmaycacdv + $dem;
                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A' . $cellcount, $tendv)
                            ->setCellValue('B' . $cellcount, 'Không có tình trạng')
                            ->setCellValue('C' . $cellcount, $dem);
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount . ':C' . $cellcount)->applyFromArray(
                            array(
                                'alignment' => array(
                                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                ),
                            )
                    );
                    $cellcount++;
                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A' . $cellcount, 'Tổng số: ' . $dem . ' máy');
                    $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    $cellcount++;
                }// ket thuc vong if cua tinh trang ko co
                else if ($tinhtrang == 'tatca') {//neu tinh trang la tat ca
                    // Truong hop tat ca cac tinh trang
                    $tongso = 0;
                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A' . $cellcount, $tendv);

                    $db1->setQuery("select * from tinh_trang_may");
                    $truyvantatca = $db1->fetchAll();

                    while ($dong = mysql_fetch_array($truyvantatca)) {

                        $tt = $dong['TEN_TINH_TRANG_MAY'];
                        $dem = 0;
                        for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                            $db1->setQuery("select * from chi_tiet_tt_may where SO_N0 = '" . $mang2[$i] . "' order by TINH_TRANG_NGAY desc");
                            $chitiet = $db1->fetchAll();
                            $ketqua1 = mysql_fetch_array($chitiet);
                            if (strtolower($ketqua1['TEN_TINH_TRANG_MAY']) == strtolower($tt))
                                $dem++;
                        }//ket thuc for 2

                        if ($dem != 0) {

                            $tongso = $tongso + $dem;
                            $tongmaycacdv = $tongmaycacdv + $dem;
                            $objPHPExcel->setActiveSheetIndex(0)
                                    ->setCellValue('B' . $cellcount, $tt)
                                    ->setCellValue('C' . $cellcount, $dem);

                            $cellcount++;
                        }
                    }//het vong while
                    //xu ly voi cac may ko co tinh trang
                    $dem = 0;
                    for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                        $db1->setQuery("select * from chi_tiet_tt_may where SO_N0 = '" . $mang2[$i] . "'");
                        $chitiet = $db1->fetchAll();
                        $ketqua2 = mysql_fetch_array($chitiet);
                        if ($ketqua2['SO_N0'] == NULL)
                            $dem++;
                    }//ket thuc for 2
                    if ($dem != 0) {

                        $tongso = $tongso + $dem;
                        $tongmaycacdv = $tongmaycacdv + $dem;
                        $objPHPExcel->setActiveSheetIndex(0)
                                ->setCellValue('B' . $cellcount, 'Không có tình trạng')
                                ->setCellValue('C' . $cellcount, $dem);
                        $cellcount++;
                    }
                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A' . $cellcount, 'Tổng số: ' . $tongso . ' máy ');
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
                    $objPHPExcel->getActiveSheet()->mergeCells('A' . $count4merge . ':A' . ($cellcount - 1));

                    $objPHPExcel->getActiveSheet()->getStyle('A' . $count4merge . ':C' . ($cellcount - 1))->applyFromArray(
                            array(
                                'alignment' => array(
                                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                ),
                            )
                    );


                    $count4merge = $cellcount + 1;
                    $cellcount++;
                }//ket thuc neu tinh trang la tat ca
            }
        }//Kiểm tra đơn vị có sở hữu máy biến áp ko?
    }//end while tat ca don vi
    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A' . $cellcount, 'Tổng số tất cả các máy : ' . $tongmaycacdv . ' máy');
    //merg dau` bang
    $objPHPExcel->getActiveSheet()->mergeCells('A6:C6');
    //merge tong so tat ca
    $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':C' . $cellcount);
    // set autoresize cho dong tieu de bang
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);


    // mau` bang
    $objPHPExcel->getActiveSheet()->getStyle('A6:' .
            $objPHPExcel->getActiveSheet()->getHighestColumn() .
            $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
            array(
                'font' => array(
                    'size' => 12,
                ),
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,
                        'color' => array('rgb' => '#000000'),
                    )
                )
            )
    );
// Set format for tableheader
    $objPHPExcel->getActiveSheet()->getStyle('A6:C7')->applyFromArray(
            array(
                'font' => array(
                    'bold' => true,
                    'size' => 13
                ),
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                )
            )
    );



    if ($dsdv != "") {
        $dsdv = ltrim($dsdv, ",");
        $cellcount = $cellcount + 1;
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A' . $cellcount, 'Danh sách các đơn vị không sở hữu MBA: ' . $dsdv);
        $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->applyFromArray(array('font' => array('name' => 'Times new Roman', 'bold' => true, 'italic' => true, 'size' => 12)));
    }
}

		// dinh font cho toan bo file
   		 $objPHPExcel->getActiveSheet()->getStyle('A1:' .
            $objPHPExcel->getActiveSheet()->getHighestColumn() .
            $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
            array(
                'font' => array(                    
                    'name' => 'Times new Roman'
                )

            )
    );
// &*&*&**&* HET TEST THEM
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Thống kê theo tình trạng');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="thongketinhtrang.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>